from sqlalchemy import exc, and_, func, or_
from sqlalchemy.orm import Session

from model.TeachingModel import CourseStageSubjectModel, CourseStageModel, SubjectSectionModel, SubjectModel, \
    CourseModel
from schema.QuerySchema import QueryBase, SubjectSectionQuery
from schema.TeachingSchema import CourseStageSubjectInfo, CourseStageInfo, SubjectSectionInfo, SubjectInfo, CourseInfo
from util.ErrorCode import ErrorCode
from util.LogHelper import Log


def add_course(courseInfo: CourseInfo, db: Session):
    courseModel = CourseModel(course_type=courseInfo.course_type, course_name=courseInfo.course_name,
                              course_content_id=courseInfo.course_content_id)
    try:
        db.add(courseModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_course failed:{courseInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_course(courseInfo: CourseInfo, db: Session):
    courseModel = db.query(CourseModel).filter(CourseModel.id == courseInfo.id).one_or_none()
    if courseModel is not None:
        if courseInfo.course_name is not None:
            courseModel.course_name = courseInfo.course_name if len(
                courseInfo.course_name) != 0 else courseModel.course_name
        if courseInfo.course_type != 0:
            courseModel.course_type = courseInfo.course_type
        if courseInfo.course_content_id != 0:
            courseModel.course_content_id = courseInfo.course_content_id

        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_course failed:{courseInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'phone:{courseInfo.id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_course_all(db: Session):
    try:
        result = db.query(CourseModel).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_course_all failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_course_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(CourseModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit

        result = db.query(CourseModel).offset((query_base.skip - 1) * query_base.limit).limit(query_base.limit).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_course_by_page failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def add_subject(subjectInfo: SubjectInfo, db: Session):
    subject_id_max = db.query(func.max(SubjectModel.subject_id)).one()
    if subject_id_max[0] is not None:
        subject_id = subject_id_max[0] + 1
    else:
        subject_id = 1
    subjectModel = SubjectModel(subject_id=subject_id, subject_name=subjectInfo.subject_name)
    try:
        db.add(subjectModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_subject failed:{subjectInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_subject(subjectInfo: SubjectInfo, db: Session):
    subjectModel = db.query(SubjectModel).filter(SubjectModel.subject_id == subjectInfo.subject_id).one_or_none()
    if subjectModel is not None:
        if subjectInfo.subject_name is not None:
            subjectModel.subject_name = subjectInfo.subject_name if len(
                subjectInfo.subject_name) != 0 else subjectModel.subject_name

        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_subject failed:{subjectInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'subject_id:{subjectInfo.subject_id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_subject_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(SubjectModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit

        result = db.query(SubjectModel).offset((query_base.skip - 1) * query_base.limit).limit(query_base.limit).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_subject_by_page failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def add_subject_section(subjectSectionInfo: SubjectSectionInfo, db: Session):
    section_id_max, sort_id_max = db.query(func.max(SubjectSectionModel.section_id),
                                           func.max(SubjectSectionModel.sort_id)).filter(
        SubjectSectionModel.subject_id == subjectSectionInfo.subject_id).one()
    if section_id_max is not None:
        section_id = section_id_max + 1
    else:
        section_id = 1

    if sort_id_max is not None:
        sort_id = sort_id_max + 1
    else:
        sort_id = 1

    subjectSectionModel = SubjectSectionModel(subject_id=subjectSectionInfo.subject_id, section_id=section_id,
                                              section_name=subjectSectionInfo.section_name,
                                              section_type=subjectSectionInfo.section_type,
                                              section_url=subjectSectionInfo.section_url,
                                              section_test_id=subjectSectionInfo.section_test_id, sort_id=sort_id)
    try:
        db.add(subjectSectionModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_subject_section failed:{subjectSectionInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_subject_section(subjectSectionInfo: SubjectSectionInfo, db: Session):
    subjectSectionModel = db.query(SubjectSectionModel).filter(
        and_(SubjectSectionModel.subject_id == subjectSectionInfo.subject_id,
             SubjectSectionModel.section_id == subjectSectionInfo.section_id)).one_or_none()
    if subjectSectionModel is not None:
        if subjectSectionInfo.section_name is not None:
            subjectSectionModel.section_name = subjectSectionInfo.section_name if len(
                subjectSectionInfo.section_name) != 0 else subjectSectionModel.section_name
        if subjectSectionInfo.section_type != 0:
            subjectSectionModel.section_type = subjectSectionInfo.section_type
        if subjectSectionInfo.section_url is not None:
            subjectSectionModel.section_url = subjectSectionInfo.section_url if len(
                subjectSectionInfo.section_url) != 0 else subjectSectionModel.section_url
        if subjectSectionInfo.section_test_id != 0:
            subjectSectionModel.section_test_id = subjectSectionInfo.section_test_id
        if subjectSectionInfo.sort_id != 0:
            subjectSectionModel.sort_id = subjectSectionInfo.sort_id

        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_subject_section failed:{subjectSectionInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(
            f'subject_id:{subjectSectionInfo.subject_id} section_id: {subjectSectionInfo.section_id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


# 根据subject_id、section_id查询subject_section
def query_subject_section_by_field(subjectSectionQuery: SubjectSectionQuery, db: Session):
    try:
        result = db.query(SubjectSectionModel).filter(and_(
            or_(SubjectSectionModel.subject_id == subjectSectionQuery.subject_id, subjectSectionQuery.subject_id == 0),
            or_(SubjectSectionModel.section_id == subjectSectionQuery.section_id, subjectSectionQuery.section_id == 0)
        )).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_subject_section_by_field failed! {subjectSectionQuery}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def delete_subject_section(subject_id: int, section_id: int, db: Session):
    try:
        subjectSectionModel = db.query(SubjectSectionModel).filter(
            and_(SubjectSectionModel.subject_id == subject_id, SubjectSectionModel.section_id == section_id)).delete()
        db.commit()
        if subjectSectionModel == 0:
            return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}
        else:
            return {'code': ErrorCode.SUCCESS.value, 'message': 'sucess'}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'delete_subject_section failed! subject_id: {subject_id} section_id: {section_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def add_course_stage(courseStageInfo: CourseStageInfo, db: Session):
    if courseStageInfo.course_content_id == 0:
        course_content_id_max = db.query(func.max(CourseStageModel.course_content_id)).one()
        if course_content_id_max[0] is not None:
            course_content_id = course_content_id_max[0] + 1
        else:
            course_content_id = 1
        sort_id = 1
    else:
        sort_id_max = db.query(func.max(CourseStageModel.sort_id)).filter(
            CourseStageModel.course_content_id == courseStageInfo.course_content_id).one()
        if sort_id_max[0] is not None:
            sort_id = sort_id_max[0] + 1
        else:
            sort_id = 1
        course_content_id = courseStageInfo.course_content_id

    courseStageModel = CourseStageModel(course_content_id=course_content_id,
                                        course_stage_name=courseStageInfo.course_stage_name, sort_id=sort_id)
    try:
        db.add(courseStageModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_course_stage failed:{courseStageInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_course_stage(courseStageInfo: CourseStageInfo, db: Session):
    courseStageModel = db.query(CourseStageModel).filter(CourseStageModel.id == courseStageInfo.id).one_or_none()
    if courseStageModel is not None:
        if courseStageInfo.course_stage_name is not None:
            courseStageModel.course_stage_name = courseStageInfo.course_stage_name if len(
                courseStageInfo.course_stage_name) != 0 else courseStageModel.course_stage_name
        if courseStageInfo.sort_id != 0:
            courseStageModel.sort_id = courseStageInfo.sort_id

        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_course_stage failed:{courseStageInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'id:{courseStageInfo.id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_course_stage_by_field(course_content_id: int, db: Session):
    try:
        result = db.query(CourseStageModel).filter(CourseStageModel.course_content_id == course_content_id).order_by(
            CourseStageModel.sort_id).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'query_course_stage_by_field failed! course_content_id: {course_content_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def delete_course_stage(course_stage_id: int, db: Session):
    try:
        courseStageModel = db.query(CourseStageModel).filter(
            CourseStageModel.id == course_stage_id).delete()
        db.commit()
        if courseStageModel == 0:
            return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}
        else:
            return {'code': ErrorCode.SUCCESS.value, 'message': 'sucess'}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'delete_scourse_stage failed! course_stage_id: {course_stage_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def add_course_stage_subject(courseStageSubjectInfo: CourseStageSubjectInfo, db: Session):
    sort_id_max = db.query(func.max(CourseStageSubjectModel.sort_id)).filter(
        CourseStageSubjectModel.course_stage_id == courseStageSubjectInfo.course_stage_id).one()
    if sort_id_max[0] is not None:
        sort_id = sort_id_max[0] + 1
    else:
        sort_id = 1

    courseStageSubjectModel = CourseStageSubjectModel(course_stage_id=courseStageSubjectInfo.course_stage_id,
                                                      subject_id=courseStageSubjectInfo.subject_id, sort_id=sort_id)
    try:
        db.add(courseStageSubjectModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_course_stage_subject failed:{courseStageSubjectInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_course_stage_subject(courseStageSubjectInfo: CourseStageSubjectInfo, db: Session):
    courseStageSubjectModel = db.query(CourseStageSubjectModel).filter(
        CourseStageSubjectModel.id == courseStageSubjectInfo.id).one_or_none()
    if courseStageSubjectModel is not None:
        if courseStageSubjectInfo.course_stage_id != 0:
            courseStageSubjectModel.course_stage_id = courseStageSubjectInfo.course_stage_id
        if courseStageSubjectInfo.subject_id != 0:
            courseStageSubjectModel.subject_id = courseStageSubjectInfo.subject_id
        if courseStageSubjectInfo.sort_id != 0:
            courseStageSubjectModel.sort_id = courseStageSubjectInfo.sort_id

        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_course_stage_subject failed:{courseStageSubjectInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'id:{courseStageSubjectInfo.id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_course_stage_subject_by_field(course_stage_id: int, db: Session):
    try:
        result = db.query(CourseStageSubjectModel).filter(
            CourseStageSubjectModel.course_stage_id == course_stage_id).order_by(CourseStageSubjectModel.sort_id).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'query_course_stage_subject_by_field failed! course_stage_id: {course_stage_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def delete_course_stage_subject(course_stage_id: int, subject_id: int, db: Session):
    try:
        courseStageSubjectModel = db.query(CourseStageSubjectModel).filter(
            and_(CourseStageSubjectModel.course_stage_id == course_stage_id,
                 CourseStageSubjectModel.subject_id == subject_id)).delete()
        db.commit()
        if courseStageSubjectModel == 0:
            return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}
        else:
            return {'code': ErrorCode.SUCCESS.value, 'message': 'sucess'}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'delete_scourse_stage_subject failed! course_stage_id: {course_stage_id} subject_id: {subject_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
